using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using StillVoiceCommon;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Collections.Generic;

/// <summary>
/// Summary description for FundingApplication
/// </summary>

namespace TeamTime.Classes
{

    public class FundingApplicationDetail : StillVoiceCommon.DataTableRow
    {
        #region properties
        public string SupplierCompanyName { get; set; }
        public string FundingFor { get; set; }

        #endregion

        public FundingApplicationDetail()
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "FundingApplicationDetail");
        }
        public new void load(string id)
        {
            base.load(id);
            System.Data.SqlClient.SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, "select * from ViewFundingApplicationDetails where ID = " + id);
            if (dr.Read())
            {
                SupplierCompanyName = dr["CompanyName"].ToString();
                FundingFor = dr["FundingFor"].ToString();
            }
            dr.Close();
            dr = null;
        }
        public DataSet getAttachments(string attachmentType)
        {
            string sql = "select * from FundingApplicationDetailAttachment " +
                            " where FundingApplicationDetailID = @FundingApplicationDetailID ";
            if (attachmentType != "")
                sql += " and AttachmentType = @AttachmentType";
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                    new SqlParameter("FundingApplicationDetailID", Convert.ToInt32(this.KeyValue)),
                    new SqlParameter("AttachmentType", attachmentType));
        }

        internal int countAttachments(string attachmentType)
        {
            string sql = "select isnull(count(*),0) from FundingApplicationDetailAttachment " +
                            " where FundingApplicationDetailID = @FundingApplicationDetailID ";
            if (attachmentType != "")
                sql += " and AttachmentType = @AttachmentType";
            return Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                    new SqlParameter("FundingApplicationDetailID", Convert.ToInt32(this.KeyValue)),
                    new SqlParameter("AttachmentType", attachmentType)));
        }



        internal bool hasAttachment(string attachmentType)
        {
            return (this.countAttachments(attachmentType) > 0);
        }
    }

    public class FundingApplicationDetailAttachment : StillVoiceCommon.DataTableRow
    {
        #region properties

        #endregion

        public FundingApplicationDetailAttachment()
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "FundingApplicationDetailAttachment");
        }
        public new void updateWithUserIDDate()
        {

            base.updateWithUserIDDate();
            if (this.fulfillsQuoteRules())
            {
                FundingApplication fundingApplication = new FundingApplication();
                fundingApplication.load(this.getFundingApplicationID());
                fundingApplication.updateWithUserIDDate("BDF:Add quote detail");
            }
            if (this.fulfillsProofOfPaymentRules())
            {
                FundingApplication fundingApplication = new FundingApplication();
                fundingApplication.load(this.getFundingApplicationID());
                fundingApplication.updateWithUserIDDate("RFP:Upload proof of payment");
            }
            if (this.fulfillsEvalFormRules())
            {
                FundingApplication fundingApplication = new FundingApplication();
                fundingApplication.load(this.getFundingApplicationID());
                fundingApplication.updateWithUserIDDate("RFP:Upload evaluation form");
            }

        }

        private bool fulfillsEvalFormRules()
        {
            bool ret = false;
            FundingApplication fundingApplication = new FundingApplication();
            fundingApplication.load(Convert.ToInt32(this.getFundingApplicationID()));
            foreach (FundingApplicationDetail fundingApplicationDetail in fundingApplication.getFundingApplicationDetails())
            {
                if (fundingApplicationDetail.hasAttachment("Evaluation Form"))
                {
                    ret = true; break;
                }
            }
            return ret;
        }

        private bool fulfillsProofOfPaymentRules()
        {
            bool ret = false;
            FundingApplication fundingApplication = new FundingApplication();
            fundingApplication.load(Convert.ToInt32(this.getFundingApplicationID()));
            foreach (FundingApplicationDetail fundingApplicationDetail in fundingApplication.getFundingApplicationDetails())
            {
                if (fundingApplicationDetail.hasAttachment("Proof Of Payment"))
                {
                    ret = true; break;
                }
            }
            return ret;
        }

        private bool fulfillsQuoteRules()
        {
            bool ret = false;
            FundingApplication fundingApplication = new FundingApplication();
            fundingApplication.load(Convert.ToInt32(this.getFundingApplicationID()));
            foreach (FundingApplicationDetail fundingApplicationDetail in fundingApplication.getFundingApplicationDetails())
            {
                //TODO: remove hardcoding - make these values parameterised
                decimal amountRequested = Convert.ToDecimal(fundingApplicationDetail.Fields["AmountRequested"]);
                if (amountRequested <= 5000)
                {
                    if (fundingApplicationDetail.countAttachments("Quote") >= 1)
                        ret = true;
                    else
                    {
                        ret = false;
                        break;
                    }
                }
                else if (amountRequested > 5000 && amountRequested <= 20000)
                {
                    if (fundingApplicationDetail.countAttachments("Quote") >= 2)
                        ret = true;
                    else
                    {
                        ret = false;
                        break;
                    }
                }
                else if (amountRequested > 20000)
                {
                    if (fundingApplicationDetail.countAttachments("Quote") >= 3)
                        ret = true;
                    else
                    {
                        ret = false;
                        break;
                    }
                }

            }
            return ret;
        }

        private Int32 getFundingApplicationID()
        {
            string sql = "select isnull(FundingApplicationID,0)   from FundingApplicationDetail where ID = @FundingApplicationDetailID";
            Int32 ID = Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                            new SqlParameter("FundingApplicationDetailID", Convert.ToInt32(this.Fields["FundingApplicationDetailID"]))));
            if (ID < 1)
                throw new Exception("FundingApplicationDetailAttachment.getFundingApplicationID: FundingApplicationID not found");
            else
                return ID;

        }


    }

    public class FundingApplication : StillVoiceCommon.DataTableRow
    {
        #region properties
        public Decimal AmountRequested { get; set; }
        public Decimal AmountGranted { get; set; }
        public string EnterpriseName { get; set; }
        public string ConsultantName { get; set; }
        public string OfficeName { get; set; }
        public string FundingFor { get; set; }
        #endregion

        public FundingApplication()
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "FundingApplication");
        }
        public new void load(Int32 id)
        {
            base.load(id);
            System.Data.SqlClient.SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                "select * from ViewFundingApplications where ID = " + id);
            if (dr.Read())
            {

                AmountRequested = Convert.ToDecimal(dr["AmountRequested"]);
                AmountGranted = Convert.ToDecimal(dr["AmountGranted"]);
                ConsultantName = dr["Consultant"].ToString();
                OfficeName = dr["Office"].ToString();
                EnterpriseName = dr["CompanyName"].ToString();
                FundingFor = dr["FundingFor"].ToString();
            }
            dr.Close();
            dr = null;
        }
        public new void updateWithUserIDDate()
        {
            this.updateWithUserIDDate("", "");
        }

        public new void updateWithUserIDDate(string workflowTaskName, string guardExpression)
        {

            if (workflowTaskName == "")
            {
                this.setStatusID();
                //base.updateWithUserIDDate(this.KeyValue == "0" ? "BDF.Application form completed" : "");
                if (this.KeyValue == "0" || this.KeyValue == "") //new record
                    base.updateWithUserIDDate("BDF:Add BDF", guardExpression);
                else
                {
                    if (this.Fields["ConsultantID"] != DBNull.Value)
                    {
                        base.updateWithUserIDDate("BDF:Assign BDF to BDC", guardExpression);
                        base.updateWithUserIDDate("RFP:Allocate RFP to BDC", guardExpression);
                    }

                    if (this.Fields["AmountPaidByOtherFunders"] != DBNull.Value
                        && this.Fields["AmountPaidByTEP"] != DBNull.Value)
                    {
                        this.Fields["PaymentRequestDate"] = DateTime.Now;
                        this.setStatusID();
                        base.updateWithUserIDDate("RFP:Capture RFP", guardExpression);
                    }
                    if (this.Fields["AmountPaidByEnterprise"] != DBNull.Value)
                    {
                        base.updateWithUserIDDate("RFP:Capture amounts paid by SMME", guardExpression);
                    }

                    else
                        base.updateWithUserIDDate();
                }
            }
            else
            {
                if (workflowTaskName == "BDF:Close BDF" && guardExpression == "Approve")
                {
                    this.Fields["ProcessedDate"] = DateTime.Now;
                }
                if (workflowTaskName == "RFP:Finance Manager release payment" && guardExpression == "Approve")
                {
                    this.Fields["PaymentProcessedDate"] = DateTime.Now;
                }
                if (workflowTaskName == "RFP:BDC close RFP" && guardExpression == "Approve")
                {
                    this.Fields["ClosedDate"] = DateTime.Now;
                }
                this.setStatusID();
                base.updateWithUserIDDate(workflowTaskName, guardExpression);
                if (workflowTaskName == "BDF:Senior Head Office Admin approval of BDF" && guardExpression == "Approve")
                {
                    if (this.AmountGranted > 15000)
                        base.updateWithUserIDDate("BDF:Split on Total threshold", "Yes");
                    else
                        base.updateWithUserIDDate("BDF:Split on Total threshold", "No");
                }
                else if (workflowTaskName == "RFP:Senior Head Office Administrator approve RFP" && guardExpression == "Approve")
                {
                    if (this.AmountGranted > 15000)
                        base.updateWithUserIDDate("RFP:Split on Total threshold", "Yes");
                    else
                        base.updateWithUserIDDate("RFP:Split on Total threshold", "No");
                }
                
                
            }

        }

        private void setStatusID()
        {
            Int16 statusID = 1;

            if (this.Fields["ApplicationDate"] != DBNull.Value && this.Fields["ProcessedDate"] == DBNull.Value)
                statusID = 1;//--BDF created 
             if (this.Fields["ApplicationDate"] != DBNull.Value && this.Fields["ProcessedDate"] != DBNull.Value
                            && this.getTotalGrantedAmount() > 0)
                statusID = 2; //--BDF approved 
             if (this.Fields["PaymentRequestDate"] != DBNull.Value && this.Fields["PaymentProcessedDate"] == DBNull.Value)
                statusID = 3; //--RFP created 
             if (this.Fields["PaymentRequestDate"] != DBNull.Value && this.Fields["PaymentProcessedDate"] != DBNull.Value &&
                    Convert.ToDecimal(this.Fields["AmountPaidByTEP"]) > 0 && this.Fields["ClosedDate"] != DBNull.Value)
                statusID = 4;//--Paid 
             if (this.Fields["ClosedDate"] != DBNull.Value && this.Fields["PaymentProcessedDate"] == DBNull.Value)
                statusID = 5; //--Declined
             if (this.Fields["ProcessedDate"] != DBNull.Value && this.Fields["ApplicationDate"] != DBNull.Value)
             {
                 if (this.Fields["ClosedDate"] == DBNull.Value && this.Fields["PaymentProcessedDate"] == DBNull.Value
                 && (DateTime.Now.AddMonths(-3) > Convert.ToDateTime(this.Fields["ProcessedDate"]) //--> set at 3 MONTH threshold from Processed date
                 || DateTime.Now.AddMonths(-6) > Convert.ToDateTime(this.Fields["ApplicationDate"])))// --> set at 6 MONTH threshold from App date)
                 {
                     statusID = 6;// --Expired
                     this.Fields["ClosedDate"] = DateTime.Now;
                 }
             }
            this.Fields["FundingApplicationStatusID"] = statusID;

        }

        private decimal getTotalGrantedAmount()
        {
            string sql = "select sum(isnull(AmountGranted,0)) from FundingApplicationDetail where FundingApplicationID = @FundingApplicationID";
            return Convert.ToDecimal(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("FundingApplicationID", Convert.ToInt32(this.KeyValue))));
        }

        internal DataSet getFundingApplicationDetails(string sortExpression)
        {
            string sql = "select *  from ViewFundingApplicationDetails where FundingApplicationID = @FundingApplicationID";

            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("FundingApplicationID", Convert.ToInt32(this.KeyValue)));
        }
        public List<FundingApplicationDetail> getFundingApplicationDetails()
        {
            List<FundingApplicationDetail> fundingAppDetails = new List<FundingApplicationDetail>();
            string sql = "select ID from ViewFundingApplicationDetails where FundingApplicationID = @FundingApplicationID";

            SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("FundingApplicationID", Convert.ToInt32(this.KeyValue)));
            while (dr.Read())
            {
                FundingApplicationDetail fad = new FundingApplicationDetail();
                fad.load(Convert.ToInt64(dr["ID"]));
                fundingAppDetails.Add(fad);
            }
            return fundingAppDetails;
        }

        internal DataSet getResponses()
        {
            string sql = "select * from ViewFundingApplicationResponses where FundingApplicationID = @FundingApplicationID order by ID desc";
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                    new SqlParameter("FundingApplicationID", Convert.ToInt32(this.KeyValue)));
        }

        internal bool hasProofOfPaymentAttachment()
        {
            throw new NotImplementedException();
        }

        internal bool hasAttachment(string p)
        {
            throw new NotImplementedException();
        }

        internal Int32 getUserIDForConsultant()
        {
            StaffMembers staffMembers = new StaffMembers();
            return staffMembers.getUserIDFromConsultantID(Convert.ToInt32(this.Fields["ConsultantID"]));
        }
    }

    public class FundingApplications
    {
        public DataSet getFundingApplicationServices(bool anyOption)
        {
            string sql = "select ID, Description  from FundingApplicationService";
            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        public DataSet searchFundingApplications(string searchFor, bool searchInNotes, string sortExpression, Int16 fundingApplicationStatusID)
        {
            StringBuilder sql = new StringBuilder("SELECT top 50 ID , EnterpriseID, CompanyName , ApplicationDate, ");
            sql.Append(" ProcessedDate,PaymentRequestDate, PaymentProcessedDate,");
            sql.Append(" ClosedDate, FundingApplicationStatus, TelephoneNumber, emailAddress, " );
            sql.Append(" Consultant, Office, AmountRequested,  AmountGranted," );            
            sql.Append("  UserNameModifiedBy,  FundingFor  " );
            sql.Append(" FROM ViewFundingApplications where 1=1 ");
            if (searchFor != "")
            {
                sql.Append(" and (CompanyName like @SearchFor+'%'");
                //sql.Append(" or ID = @SearchFor ");
                if (searchInNotes)
                {
                    sql.Append(" or");
                    sql.Append(" Notes like '%'+@SearchFor+'%' ");
                }
                sql.Append(") ");
            }
            if (fundingApplicationStatusID > 0)
            {
                sql.Append(" and FundingApplicationStatusID = @FundingApplicationStatusID ");
            }

            if (sortExpression != "")
            {
                sql.Append(" order by " + sortExpression);
            }
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql.ToString(),
                new SqlParameter("SearchFor", searchFor),
                new SqlParameter("FundingApplicationStatusID", fundingApplicationStatusID));

            //return sqlDB.getDS(sql.ToString());
        }

        /*
        public bool duplicateFundingApplicationExists(string FundingApplicationName, string ID)
        {
            bool ret = false;
            string sql = "select ID from ViewFundingApplications where CompanyName = '" + FundingApplicationName + "' ";
            if (ID != "") { sql += " and ID <> '" + ID + "'"; }//same name different id
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
            ret = dr.Read();
            dr.Close();
            return ret;
        }

        public int getDuplicateFundingApplicationID(string FundingApplicationName, string ID)
        {
            int ret = 0;
            string sql = "select ID from ViewFundingApplications where CompanyID = '" + FundingApplicationName + "' ";
            if (ID != "") { sql += " and ID <> '" + ID + "'"; }//same name different id
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
            if (dr.Read())
            {
                ret = Convert.ToInt32(dr[0]);
            }
            dr.Close();
            return ret;
        }*/

    }

    public class FundingApplicationResponse : StillVoiceCommon.DataTableRow
    {
        #region properties
        public string StaffMember { get; set; }
        #endregion

        public FundingApplicationResponse()
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "FundingApplicationResponse");
        }
        public new void load(Int32 id)
        {
            base.load(id);
            System.Data.SqlClient.SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text,
                "select * from ViewFundingApplicationResponses where ID = " + id);
            if (dr.Read())
            {
                StaffMember = dr["StaffMember"].ToString();
            }
            dr.Close();
            dr = null;
        }

        public new void updateWithUserIDDate(string taskName)
        {
            base.updateWithUserIDDate(taskName);
        }
    }
}
